/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.una.scece.gestores;

import com.una.scece.conexion.Conexion;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Erick
 */
public class gestorUtiles {

    private static PreparedStatement stmt = null;
    private static ResultSet rs = null;
    private static Connection conn = null;
    private static Conexion cx = new Conexion();

    public static ArrayList<String> verIdentificacionEstudiantes() throws Exception {
        ArrayList<String> lista = new ArrayList<String>();
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("call verIdentificacionEstudiante()");
        rs = stmt.executeQuery();
        while (rs.next()) {
            dato = rs.getString("identificacion") + " - " + rs.getString("nombre_completo");
            lista.add(dato);
        }
        rs.close();
        stmt.close();
        return lista;
    }

    public static ArrayList<String> verIdentificacionFuncionarios() throws Exception {
        ArrayList<String> lista = new ArrayList<String>();
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("call verIdentificacionFuncionario()");
        rs = stmt.executeQuery();
        while (rs.next()) {
            dato = rs.getString("identificacion") + " - " + rs.getString("nombre_completo");
            lista.add(dato);
        }
        rs.close();
        stmt.close();
        return lista;
    }

    public static String reporteFechas(Date pFechaInicio, Date pFechaFinal) throws Exception {
        String info = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("call reportePagoFechas(?,?)");
        stmt.setDate(1, pFechaInicio);
        stmt.setDate(2, pFechaFinal);
        rs = stmt.executeQuery();
        rs.last();
        int tam = rs.getRow();
        rs.beforeFirst();
        if (tam > 0) {
            info = "<div class=\"table\"> ";
            info += "<p><strong>Informacion del Reporte</strong></p>"
                    + "<table class=\"listing\" cellpadding=\"0\" cellspacing=\"0\">";
            info += "<tr><th>Identificaci&oacute;n</th><th>Nombre Completo</th><th>Comprobante</th><th>Fecha de Pago</th><th>Monto de pago</th><th>Trimestre</th><th>A&ntilde;o</th></tr>";
            while (rs.next()) {
                info += "<tr><td>" + rs.getString("identificacion") + "</td><td>" + rs.getString("nombre_completo") + "</td><td><a href=\"" + rs.getString("ruta_comprobante") + "\" target=\"_blank\">Ver comprobante</a></td><td>" + String.valueOf(rs.getDate("fecha_pago")) + "</td><td>" + rs.getDouble("monto_pago") + "</td><td>" + rs.getString("ciclo") + "</td><td>" + rs.getInt("anio") + "</td></tr>";
            }
            info += "</table></div>";
        } else {
            info = "<p><strong>No se encontraron registros que coincidan</br>"
                    + "con las fechas indicadas.</strong></p>";
        }

        rs.close();
        stmt.close();
        return info;
    }

    public static String reporteMorosos(int pAnio, String pCiclo) throws Exception {
        String info = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("call reporteMorosos(?,?)");
        stmt.setInt(1, pAnio);
        stmt.setString(2, pCiclo);
        rs = stmt.executeQuery();
        rs.last();
        int tam = rs.getRow();
        rs.beforeFirst();
        if (tam > 0) {
            info = "<div class=\"table\"> ";
            info += "<p><strong>Informacion del Reporte</strong></p>"
                    + "<table class=\"listing\" cellpadding=\"0\" cellspacing=\"0\">";
            info += "<tr><th>Identificaci&oacute;n</th><th>Nombre Completo</th><th>Correo Electr&oacute;nico</th><th>Tel&eacute;fono de Oficina</th><th>Tel&eacute;fono de Habitaci&oacute;n</th><th>Tel&eacute;fono Celular</th><th>Direcci&oacute;n</th></tr>";
            while (rs.next()) {
                info += "<tr><td>" + rs.getString("identificacion") + "</td><td>" + rs.getString("nombre_completo") + "</td><td>" + rs.getString("correo_electronico") + "</td><td>" + rs.getString("telefono_oficina") + "</td><td>" + rs.getString("telefono_habitacion") + "</td><td>" + rs.getString("telefono_celular") + "</td><td>" + rs.getString("direccion") + "</td></tr>";
            }
            info += "</table>";
            
            info += "<div class=\"table\"><table class=\"listing\" cellpadding=\"0\" cellspacing=\"0\"><tr><th>Observaciones: &nbsp;</th><td><textarea rows=\"7\" cols=\"45\" class=\"text\" name=\"observaciones\" id=\"observaciones\"></textarea></td></table></div>";
            info += "<div class=\"boton\" align=\"right\"><a><input type=\"button\" name=\"guardar\" class=\"boton\" value=\"Enviar Notificacion\" onclick=\"return enviarNotificacion();\"/></a></div></div>";
        } else {
            info = "<p><strong>No se encontraron registros de morosos actualmente</strong></p>";
        }
        rs.close();
        stmt.close();
        return info;
    }

    public static ArrayList<String> correoMorosos(Integer pAnio, String pCiclo) throws Exception {
        ArrayList<String> lista = new ArrayList<String>();
        conn = cx.conectar();
        stmt = conn.prepareStatement("call reporteMorosos(?,?)");
        stmt.setInt(1, pAnio);
        stmt.setString(2, pCiclo);
        rs = stmt.executeQuery();
        while (rs.next()) {
            lista.add(rs.getString("correo_electronico"));
        }
        rs.close();
        stmt.close();
        return lista;
    }

    public static ArrayList<String> verIdentificacionCursos(int pAnio, String pCiclo) throws Exception {
        ArrayList<String> lista = new ArrayList<String>();
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("select * from Cursos where anio = ? and ciclo = ?");
        stmt.setInt(1, pAnio);
        stmt.setString(2, pCiclo);
        rs = stmt.executeQuery();
        while (rs.next()) {
            dato = rs.getString("codigo") + " - " + rs.getString("nombre_curso");
            lista.add(dato);
        }
        rs.close();
        stmt.close();
        return lista;
    }

    public static ArrayList<Double> verCursosEstudiante(String pIdentificacion, int pAnio, String pCiclo) throws Exception {
        ArrayList<Double> lista = new ArrayList<Double>();
        conn = cx.conectar();
        stmt = conn.prepareStatement("CALL listarCursosEstudiante(?,?,?)");
        stmt.setString(1, pIdentificacion);
        stmt.setInt(2, pAnio);
        stmt.setString(3, pCiclo);
        rs = stmt.executeQuery();
        while (rs.next()) {
            lista.add(rs.getDouble("costo"));
        }
        rs.close();
        stmt.close();
        return lista;
    }

    public static String verCursosEstudiante2(String pIdentificacion, int pAnio, String pCiclo) throws Exception {
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("CALL listarCursosEstudiante(?,?,?)");
        stmt.setString(1, pIdentificacion);
        stmt.setInt(2, pAnio);
        stmt.setString(3, pCiclo);
        rs = stmt.executeQuery();
        while (rs.next()) {
            dato += rs.getString("codigo") + " - " + rs.getString("nombre_curso") + "|";
        }
        rs.close();
        stmt.close();
        return dato;
    }

    public static String cargarPendientes() throws Exception {
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("CALL reportePagosPendientes()");
        rs = stmt.executeQuery();
        while (rs.next()) {
            dato +=  rs.getString("identificacion") + " - " + rs.getString("nombre_completo") + " - " + String.valueOf(rs.getDate("fecha_pago")) + "|";
        }
        rs.close();
        stmt.close();
        return dato;
    }

    public static String cargarReportePendiente(String pIdentificacion, Date pFecha) throws Exception {
        String dato = "";
        conn = cx.conectar();
        stmt = conn.prepareStatement("call cargarReportePagosPendientes(?,?)");
        stmt.setString(1, pIdentificacion);
        stmt.setDate(2, pFecha);
        rs = stmt.executeQuery();
        rs.next();
        dato += "<form id=\"reportePendiente\"><table class=\"listing\" cellpadding=\"0\" cellspacing=\"0\">";
        dato += "<tr><th>Identificacion: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + rs.getString("identificacion") + "</span></td></tr>";
        dato += "<tr><th>Nombre Completo: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + rs.getString("nombre_completo") + "</span></td></tr>";
        dato += "<tr><th>Correo Electr&oacute;nico: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + rs.getString("correo_electronico") + "</span></td></tr>";
        dato += "<tr><th>Comprobante: &nbsp;</th><td width=\"75%\"><span>&nbsp;<a href=\"" + rs.getString("ruta_comprobante") + "\" target=\"_blank\">Ver Comprobante</a></span></td></tr>";
        dato += "<tr><th>Fecha de Pago: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + String.valueOf(rs.getDate("fecha_pago")) + "</span></td></tr>";
        dato += "<tr><th>Monto de Pago: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + String.valueOf(rs.getDouble("monto_pago")) + "</span></td></tr>";
        dato += "<tr><th>A&ntilde;o: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + String.valueOf(rs.getInt("anio")) + "</span></td></tr>";
        dato += "<tr><th>Trimestre: &nbsp;</th><td width=\"75%\"><span>&nbsp;" + rs.getString("ciclo") + "</span></td></tr>";
        dato += "<tr><th>Observaciones: &nbsp;</th><td><textarea rows=\"7\" cols=\"45\" class=\"text\" name=\"observaciones\" id=\"observaciones\"></textarea></td>";
        dato += "</table>";
        dato += "<div class=\"boton\" align=\"right\"><a><input type=\"button\" name=\"aprobar\" class=\"boton\" value=\"Aprobar\" onclick=\"return aprobarPago();\"/></a><a><input type=\"button\" name=\"rechazar\" value=\"Rechazar\" onclick=\"return rechazarPago();\" class=\"boton\"/></a>";
        dato += "</form>";
        return dato;
    }
}
